using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// Summary description for ProjectManager
/// </summary>
public class ProjectManager
{
    #region gets

    /// <summary>
    /// gets a list of all projects
    /// </summary>
    /// <returns>List of projects or null if empty</returns>
    public static DataTable GetProjects()
    {
        //List<Project> lisReturnCollection = null;
        DataTable dtaReturnTable = null;

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("GetProjects", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        dtaReturnTable = new DataTable();

                        for (int intCounter = 0; intCounter < reader.FieldCount; intCounter++)
                        {
                            dtaReturnTable.Columns.Add(reader.GetName(intCounter));
                        }

                        //Project proTemProject;
                        //lisReturnCollection = new List<Project>();

                        DataRow droTemp;

                        do
                        {


                            droTemp = dtaReturnTable.NewRow();

                            for (int intCounter = 0; intCounter < reader.FieldCount; intCounter++)
                            {
                                droTemp[reader.GetName(intCounter)] = reader[intCounter];
                            }

                            dtaReturnTable.Rows.Add(droTemp);

                            //proTemProject = new Project();

                            //proTemProject.ID = Convert.ToInt32(reader["PRO_ID"]);
                            //proTemProject.AlbumID = Convert.ToInt32(reader["PRO_ALBUMID"]);
                            //proTemProject.CatID = Convert.ToInt32(reader["PRO_CAT_ID"]);
                            //proTemProject.Created = Convert.ToDateTime(reader["PRO_DATE"]);
                            //proTemProject.FileID = Convert.ToInt32(reader["PRO_FIL_ID"]);
                            //proTemProject.LongDesc = reader["PRO_LONG_DESC"].ToString();
                            //proTemProject.PhotoID = Convert.ToInt32(reader["PRO_THUMB_PHOTOID"]);
                            //proTemProject.ShortDesc = reader["PRO_SHORT_DESC"].ToString();
                            //proTemProject.StatusID = Convert.ToInt32(reader["PRO_STATUS_PST_ID"]);
                            //proTemProject.Title = reader["PRO_TITLE"].ToString();
                            //proTemProject.Updated = Convert.ToDateTime(reader["PRO_DATE_UPDATED"]);
                            //proTemProject.URL = reader["PRO_URL"].ToString();
                            //proTemProject.WhitePaperID = Convert.ToInt32(reader["PRO_WHITE_PAPER_ID"]);
                            //proTemProject.Category = reader["PCA_TITLE"].ToString();
                            //proTemProject.Status = reader["PST_TITLE"].ToString();
                            //proTemProject.PhotoTitle = reader["PhotoTitle"].ToString();
                            //proTemProject.AlbumTitle = reader["AlbumTitle"].ToString();

                            //lisReturnCollection.Add(proTemProject);

                        } while (reader.Read());// end of the read while loop

                    }// end of the if check to see if anything was found
                }// end reader using              
            }// end command using
        }// end connection using

        return dtaReturnTable;
    }//end of the GetProjects method
    
    /// <summary>
    /// retrives a project
    /// </summary>
    /// <param name="ID">Project to retrieve</param>
    /// <returns>a Project or null if not found</returns>
    public static Project GetProject(int ID)
    { 
        Project proReturn = null;
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("GetProject", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("PRO_ID", ID ));

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        proReturn = new Project();

                        proReturn.ID = Convert.ToInt32(reader["PRO_ID"]);
                        proReturn.AlbumID = Convert.ToInt32(reader["PRO_ALBUMID"]);
                        proReturn.CatID = Convert.ToInt32(reader["PRO_CAT_ID"]);
                        proReturn.Created = Convert.ToDateTime(reader["PRO_DATE"]);
                        proReturn.Display = reader["PRO_DISPLAY_URL"].ToString();
                        proReturn.FileID = Convert.ToInt32(((reader["PRO_FIL_ID"]== DBNull.Value)? "-1" : reader["PRO_FIL_ID"]));

                        proReturn.LongDesc = reader["PRO_LONG_DESC"].ToString();
                        proReturn.PhotoID = Convert.ToInt32(((reader["PRO_THUMB_PHOTOID"] == DBNull.Value) ? "-1" : reader["PRO_THUMB_PHOTOID"]));
                        proReturn.ShortDesc = reader["PRO_SHORT_DESC"].ToString();
                        proReturn.StatusID = Convert.ToInt32(((reader["PRO_STATUS_PST_ID"]== DBNull.Value)? "-1" : reader["PRO_STATUS_PST_ID"]));
                        proReturn.Title = reader["PRO_TITLE"].ToString();
                        proReturn.Updated = Convert.ToDateTime(((reader["PRO_DATE_UPDATED"]== DBNull.Value)? "-1" : reader["PRO_DATE_UPDATED"]));
                        proReturn.URL = reader["PRO_URL"].ToString();
                        proReturn.WhitePaperID = Convert.ToInt32(((reader["PRO_WHITE_PAPER_ID"] == DBNull.Value) ? "-1" : reader["PRO_WHITE_PAPER_ID"]));
                        proReturn.Category = reader["PCA_TITLE"].ToString();
                        proReturn.Status = reader["PST_TITLE"].ToString();
                        proReturn.PhotoTitle = reader["PhotoTitle"].ToString();
                        proReturn.AlbumTitle = reader["AlbumTitle"].ToString();
                        proReturn.FileName = reader["Name"].ToString();

                    }// end of the if check to see if anything was found
                }// end reader using              
            }// end command using
        }// end connection using
        return proReturn;

    }// end of the GetProject method

    public static DataTable GetProjectStatuses()
    {
        //List<ListItem<int, string, string>> lisReturnCollection = null;
        DataTable dtaReturnTable = null;

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("GetProjectStatuses", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        //ListItem<int, string, string> litTemItem;
                        //lisReturnCollection = new List<ListItem<int, string, string>>();
                        dtaReturnTable = new DataTable();

                        DataRow droTemp;

                        for (int intCounter = 0; intCounter < reader.FieldCount; intCounter++)
                        {
                            dtaReturnTable.Columns.Add(reader.GetName(intCounter));
                        }

                        do
                        {

                            droTemp = dtaReturnTable.NewRow();

                            for (int intCounter = 0; intCounter < reader.FieldCount; intCounter++)
                            {
                                droTemp[reader.GetName(intCounter)] = reader[intCounter];
                            }

                            dtaReturnTable.Rows.Add(droTemp);


                            //litTemItem = new ListItem<int, string, string>();

                            //litTemItem.Key = Convert.ToInt32(reader["PST_ID"].ToString());
                            //litTemItem.Value = reader["PST_TITLE"].ToString();
                            //litTemItem.Description = reader["PST_DESCRIPTION"].ToString();

                            //lisReturnCollection.Add(litTemItem);

                        } while (reader.Read()) ;// end of the read while loop

                    }// end of the if check to see if anything was found
                }// end reader using              
            }// end command using
        }// end connection using

        //return lisReturnCollection;
        return dtaReturnTable;
    }//end of the GetProjectStatuses method

    /// <summary>
    /// Gets a single project status
    /// </summary>
    /// <param name="ID">status ID</param>
    /// <returns>ListItem<int(ID), string(Title), string(Description)> or null if empty</returns>
    public static ListItem<int, string, string> GetProjectStatus(int ID)
    {
        ListItem<int, string, string> litReturnItem = null;

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("GetProjectStatus", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("PST_ID", ID));

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        litReturnItem = new ListItem<int, string, string>();

                        litReturnItem.Key = Convert.ToInt32(reader["PST_ID"]);
                        litReturnItem.Value = reader["PST_TITLE"].ToString();
                        litReturnItem.Description = reader["PST_DESCRIPTION"].ToString();                        

                    }// end of the read while loop
                }// end reader using              
            }// end command using
        }// end connection using

        return litReturnItem;
    }// end of the GetProjectStatus method

    public static DataTable GetProjectCategories()
    {
        //List<ListItem<int, string, string>> lisReturnCollection = null;
        DataTable dtaReturnTable = null;

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("GetProjectCategories", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        //ListItem<int, string, string> litTemItem;
                        //lisReturnCollection = new List<ListItem<int, string, string>>();
                        dtaReturnTable = new DataTable();

                        DataRow droTemp;

                        for (int intCounter = 0; intCounter < reader.FieldCount; intCounter++)
                        {
                            dtaReturnTable.Columns.Add(reader.GetName(intCounter));
                        }

                        do
                        {

                            droTemp = dtaReturnTable.NewRow();

                            for (int intCounter = 0; intCounter < reader.FieldCount; intCounter++)
                            {
                                droTemp[reader.GetName(intCounter)] = reader[intCounter];
                            }

                            dtaReturnTable.Rows.Add(droTemp);

                            //litTemItem = new ListItem<int, string, string>();

                            //litTemItem.Key = Convert.ToInt32(reader["PCA_ID"].ToString());
                            //litTemItem.Value = reader["PCA_TITLE"].ToString();
                            //litTemItem.Description = reader["PCA_DESCRIPTION"].ToString();

                            //lisReturnCollection.Add(litTemItem);

                        } while (reader.Read()) ;// end of the read while loop

                    }// end of the if check to see if anything was found
                }// end reader using              
            }// end command using
        }// end connection using

        return dtaReturnTable;
        //return lisReturnCollection;
    }//end of the GetProjectCategories method

    /// <summary>
    /// Gets a single project category
    /// </summary>
    /// <param name="ID">category ID</param>
    /// <returns>ListItem<int(ID), string(Title), string(Description)> or null if empty</returns>
    public static ListItem<int, string, string> GetProjectCategory(int ID)
    {
        ListItem<int, string, string> litReturnItem = null;

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("GetProjectCategory", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("PCA_ID", ID));

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        litReturnItem = new ListItem<int, string, string>();

                        litReturnItem.Key = Convert.ToInt32(reader["PCA_ID"]);
                        litReturnItem.Value = reader["PCA_TITLE"].ToString();
                        litReturnItem.Description = reader["PCA_DESCRIPTION"].ToString();

                    }// end of the read while loop
                }// end reader using              
            }// end command using
        }// end connection using

        return litReturnItem;
    }// end of the GetProjectCategory method

    #endregion

    #region updates
    public static bool UpdateProject(Project project)
    {
        int intRowsUpdated = 0;
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("UpdateProject", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@PRO_ID", project.ID));
                command.Parameters.Add(new SqlParameter("@PRO_ALBUMID", project.AlbumID));
                command.Parameters.Add(new SqlParameter("@PRO_CAT_ID", project.CatID));
                command.Parameters.Add(new SqlParameter("@PRO_FIL_ID", project.FileID));
                command.Parameters.Add(new SqlParameter("@PRO_LONG_DESC", project.LongDesc));
                command.Parameters.Add(new SqlParameter("@PRO_THUMB_PHOTOID", project.PhotoID));
                command.Parameters.Add(new SqlParameter("@PRO_SHORT_DESC", project.ShortDesc));
                command.Parameters.Add(new SqlParameter("@PRO_STATUS_PST_ID", project.StatusID));
                command.Parameters.Add(new SqlParameter("@PRO_TITLE", project.Title));
                command.Parameters.Add(new SqlParameter("@PRO_URL", project.URL));
                command.Parameters.Add(new SqlParameter("@PRO_DISPLAY_URL", project.Display));
                command.Parameters.Add(new SqlParameter("@PRO_WHITE_PAPER_ID", project.WhitePaperID));

                connection.Open();
                intRowsUpdated = command.ExecuteNonQuery();


            }// end command using
        }// end connection using

        return ((intRowsUpdated == 0) ? false : true);
       
    }// end of the UpdateProject method

    public static bool UpdateProjectStatus(int PST_ID, string PST_TITLE, string PST_DESCRIPTION)
    {
        int intRowsUpdated = 0;
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("UpdateProjectStatus", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@PST_ID", PST_ID));
                command.Parameters.Add(new SqlParameter("@PST_TITLE", PST_TITLE));
                command.Parameters.Add(new SqlParameter("@PST_DESCRIPTION", PST_DESCRIPTION));
                
                connection.Open();
                intRowsUpdated = command.ExecuteNonQuery();


            }// end command using
        }// end connection using

        return ((intRowsUpdated == 0) ? false : true);
    }// end of the UpdateProjectStatus method

    public static bool UpdateProjectCategory(int PCA_ID, string PCA_TITLE, string PCA_DESCRIPTION)
    {
        int intRowsUpdated = 0;
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("UpdateProjectCategory", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@PCA_ID", PCA_ID));
                command.Parameters.Add(new SqlParameter("@PCA_TITLE", PCA_TITLE));
                command.Parameters.Add(new SqlParameter("@PCA_DESCRIPTION", PCA_DESCRIPTION));

                connection.Open();
                intRowsUpdated = command.ExecuteNonQuery();


            }// end command using
        }// end connection using

        return ((intRowsUpdated == 0) ? false : true);
    }// end of the UpdateProjectCategory method
    #endregion

    #region inserts
    public static bool InsertProject(Project project)
    {
        int intRowsUpdated = 0;
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("AddProject", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@PRO_ALBUMID", project.AlbumID));
                command.Parameters.Add(new SqlParameter("@PRO_CAT_ID", project.CatID));
                command.Parameters.Add(new SqlParameter("@PRO_FIL_ID", project.FileID));
                command.Parameters.Add(new SqlParameter("@PRO_LONG_DESC", project.LongDesc));
                command.Parameters.Add(new SqlParameter("@PRO_THUMB_PHOTOID", project.PhotoID));
                command.Parameters.Add(new SqlParameter("@PRO_SHORT_DESC", project.ShortDesc));
                command.Parameters.Add(new SqlParameter("@PRO_STATUS_PST_ID", project.StatusID));
                command.Parameters.Add(new SqlParameter("@PRO_TITLE", project.Title));
                command.Parameters.Add(new SqlParameter("@PRO_URL", project.URL));
                command.Parameters.Add(new SqlParameter("@PRO_DISPLAY_URL", project.Display));
                command.Parameters.Add(new SqlParameter("@PRO_WHITE_PAPER_ID", project.WhitePaperID));

                connection.Open();
                intRowsUpdated = command.ExecuteNonQuery();


            }// end command using
        }// end connection using

        return ((intRowsUpdated == 0) ? false : true);
    }// end of the InsertProject method

    public static bool InsertProjectStatus(string Title, string Desc)
    {
        int intRowsUpdated = 0;
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("AddProjectStatus", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@PST_TITLE", Title));
                command.Parameters.Add(new SqlParameter("@PST_DESCRIPTION", Desc));

                connection.Open();
                intRowsUpdated = command.ExecuteNonQuery();


            }// end command using
        }// end connection using

        return ((intRowsUpdated == 0) ? false : true);
    }// end of the InsertProjectStatus method

    public static bool InsertProjectCategory(string Title, string Desc)
    {
        int intRowsUpdated = 0;
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("AddProjectCategory", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@PCA_TITLE", Title));
                command.Parameters.Add(new SqlParameter("@PCA_DESCRIPTION", Desc));

                connection.Open();
                intRowsUpdated = command.ExecuteNonQuery();


            }// end command using
        }// end connection using

        return ((intRowsUpdated == 0) ? false : true);
    }// end of the InsertProjectCategory method
    #endregion

    #region deletes

    public static bool DeleteProject(int PRO_ID)
    {
        int intRowsUpdated = 0;
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("RemoveProject", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@PRO_ID", PRO_ID));

                connection.Open();
                intRowsUpdated = command.ExecuteNonQuery();

            }// end command using
        }// end connection using

        return ((intRowsUpdated == 0) ? false : true);
    }// end of the DeleteProject method

    public static bool DeleteProjectStatus(int PST_ID)
    {
        int intRowsUpdated = 0;
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("RemoveProjectStatus", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@PST_ID", PST_ID));

                connection.Open();
                intRowsUpdated = command.ExecuteNonQuery();

            }// end command using
        }// end connection using

        return ((intRowsUpdated == 0) ? false : true);
    }// end of the DeleteProjectStatus method

    public static bool DeleteProjectCategory(int PCA_ID)
    {
        int intRowsUpdated = 0;
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("RemoveProjectCategory", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@PCA_ID", PCA_ID));

                connection.Open();
                intRowsUpdated = command.ExecuteNonQuery();

            }// end command using
        }// end connection using

        return ((intRowsUpdated == 0) ? false : true);
    }// end of the DeleteProjectCategory method
    #endregion

}// end of the ProjectManager class
